/*
 * @author rsundara.in
 * 
 * @version 1.2
 * 
 * Development Environment : JDeveloper 2.0 Name of the Application : LongRawSample.java Creation/Modification History :
 * 
 * rsundara.in 28-Dec-1998 Created neshastr 22-May-2002 Certified on Oracle9i JDeveloper Savitha 10-Jan-2003 Certified
 * on Linux Included checkTables() method Changed getLogo method to write the gif file to user.home directory.
 */
package oracle.otnsamples.jdbc.longraw;

// java utility classes
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import oracle.jdbc.pool.OracleDataSource;

/**
 * This sample illustrates accessing Oracle LONGRAW datatype using JDBC. Long raw column is normally used for storing
 * large binary data like image, sound files upto a maximum of 2GB.
 * 
 * In this sample, image files stored in a LONGRAW column are retrieved and displayed on the front end interface. The
 * table OTN_AIRLINE_LONGRAW is used to store Long Raw Data
 * 
 * OTN_AIRLINE_LONGRAW is created and populated with sample image files, by calling methods from populateTable class
 * 
 * The gui for this sample is handled in LongRawFrame.java
 * 
 * The sample uses the JDBC-Thin driver.
 */
public class LongRawSample {

    /** Database Connection Object */
    private Connection connection = null;

    /** gui handler for this sample */
    private LongRawFrame gui;

    /** create PopulateTable object */
    private PopulateTable createTab;

    /**
     * Constructor. Instantiates gui.
     */
    public LongRawSample() {
        try {
            gui = new LongRawFrame(this); // Instantiate gui
            gui.setVisible(true);
        } catch (Exception e) { // Trap general Errors
            e.printStackTrace();
        }
    }

    /**
     * Main entry point for the class. Instantiates class and sets up the database connection.The selection of airline
     * names are done here.
     */
    public static void main(String[] args) throws SQLException {
        LongRawSample lrs = new LongRawSample();// Instantiate application class

        // Setup the db connection
        lrs.dbConnection();

        // check if connection exists, else create it.
        if (lrs.connection != null) {

            // Check if 'otn_airline_longraw' table is present, else create it
            lrs.checkTables();

            // Fills the JTable with records
            lrs.selectAirlines();
        }
    }

    /**
     * Dispatches the gui events to the appropriate method, which performs the required JDBC operations. This method is
     * invoked when event occurs in the gui (like table Selection, Button clicks etc.). This method is invoked from the
     * setupListeners section of LongRawFrame.java
     */
    public void dispatchEvent(String eventName) {

        // Dispatch Event
        if (eventName.equals("SELECT")) {
            // Get the user selected airline code
            int selectedRow = gui.table.getSelectedRow();
            String code = (String) gui.tablemodel.getValueAt(selectedRow, 0);

            // Retrieve the gif file from the LONGRAW column in the database
            String nameOfGif = getLogo(code);

            if (nameOfGif != null)
                gui.drawLogo(nameOfGif); // Draw the image
        }
        if (eventName.equals("EXIT"))
            exitApplication();
    }

    /**
     * This method reads a properties file which is passed as the parameter to it and load it into a java Properties
     * object and returns it.
     */

    private Properties loadParams(String file) throws IOException {

        // Loads a ResourceBundle and creates Properties from it
        Properties prop = new Properties();
        prop.put("HostName", "127.0.0.1");
        prop.put("SID", "dengues");
        prop.put("Port", "1521");
        prop.put("UserName", "root");
        prop.put("Password", "root");
        return prop;
    }

    /**
     * Creates a database connection object using DataSource object. Please substitute the database connection
     * parameters with appropriate values in Connection.properties file
     */
    private void dbConnection() {
        try {
            gui.putStatus("Trying to connect to the Database");

            // Load the properties file to get the connection information
            Properties prop = this.loadParams("Connection");

            // Create a OracleDataSource instance
            OracleDataSource ods = new OracleDataSource();

            // Sets the driver type
            ods.setDriverType("thin");

            // Sets the database server name
            ods.setServerName((String) prop.get("HostName"));

            // Sets the database name
            ods.setDatabaseName((String) prop.get("SID"));

            // Sets the port number
            ods.setPortNumber(new Integer((String) prop.get("Port")).intValue());

            // Sets the user name
            ods.setUser((String) prop.get("UserName"));

            // Sets the password
            ods.setPassword((String) prop.get("Password"));
            connection = ods.getConnection();
            gui.putStatus(" Connected to " + prop.get("SID") + " Database as " + prop.get("UserName"));

        } catch (SQLException ex) { // Trap SQL errors
            gui.putStatus("Error in Connecting to the Database " + '\n' + ex.toString());
        } catch (IOException ex) { // Trap IO errors
            gui.putStatus("Error in reading the properties file " + '\n' + ex.toString());
        }
    }

    /**
     * Checks if the table ('otn_airline_longraw') is present, else creates it. Look into PopulateTable.java class
     * methods for more details
     */
    private void checkTables() {
        Statement stmt = null;
        ResultSet rset = null;
        try {
            stmt = connection.createStatement();

            // check from User_tables data dictionary table to see if the table exists.
            rset = stmt.executeQuery(" SELECT Table_Name FROM User_Tables " + " WHERE Table_Name = 'OTN_AIRLINE_LONGRAW' ");

            // if the resultset of the above query does not have any record, it means
            // table is not existing. So the table is created.
            if (!rset.next()) {
                // call the class to create the table
                createTab = new PopulateTable(connection, gui);
                createTab.createSchemaTable();
                gui.putStatus(" otn_airline_longraw Table created. ");
            }
        } catch (SQLException sqlEx) {
            gui.putStatus(" Could not create table otn_airline_longraw : " + sqlEx.toString());
        } finally {
            try {
                if (rset != null)
                    rset.close();
                if (stmt != null)
                    stmt.close();
            } catch (SQLException ex) {
            }
        }
    }

    /**
     * This method retrieves the logo of the selected airline from the long raw column in the table.
     */
    private String getLogo(String lcode) {
        String name = null;
        InputStream gifdata = null;
        String userHome = null;
        String fullName = null;
        try {
            // Query the logo of the selected airline
            String query = "SELECT name,airline_logo FROM otn_airline_longraw WHERE code=?";

            // Create a PreparedStatement object for executing the query
            PreparedStatement pst = connection.prepareStatement(query);

            // bind the parameter with code value
            pst.setString(1, lcode);

            // Obtain the result-set for the selected airline record
            ResultSet result = pst.executeQuery();

            // get user home folder name
            userHome = System.getProperty("user.home");

            if (result.next()) {

                // Fetch column values
                name = result.getString(1); // Obtain the airline code

                // append the file name with user home directory, file separator and
                // file extension GIF
                fullName = userHome + File.separator + name + ".gif";

                // LONGRAW data can be accessed in two ways:
                // 1) By retrieving all the data at once (using getBytes method)
                // 2) By using streams. The LONGRAW data is made available to the program
                // as a stream, and the data can be retrieved chunk by chunk, which is
                // more eficient in terms of memory usage
                // In this sample we illustrate retrieval using streams method.
                gifdata = result.getBinaryStream(2);

                // create new file
                File gifFile = new File(fullName);

                // Write the byte array into a local file
                FileOutputStream file = new FileOutputStream(gifFile);

                int chunk = 0;
                // write to the local file until image (LONGRAW) data is found
                while ((chunk = gifdata.read()) != -1) {
                    file.write(chunk);
                }

                // flush the data
                file.flush();
                file.close(); // close the file

                pst.close(); // closing the statement object
                gui.putStatus("Image obtained from database");
            }
        } catch (Exception ex) { // Trap IO Errors
            gui.putStatus(ex.toString());
        }
        return fullName;
    }

    /**
     * This method selects the airline records from the otn_airline_longraw and fills the front end JTable with the
     * records.
     */
    private void selectAirlines() {
        ResultSet result = null;
        try {
            // Create a statement for executing the query
            Statement statement = connection.createStatement();

            // The query string
            String qry = "SELECT code,name,partner FROM otn_airline_longraw";

            // Execute the query and obtain the result set.
            result = statement.executeQuery(qry);
            gui.tablemodel.clearTable(); // Clear JTable in gui
            while (result.next()) { // Point result set to next row
                // Retrieve column values for this row
                String code = result.getString(1);
                String name = result.getString(2);
                String partner = result.getString(3);
                gui.addToJTable(code, name, partner); // Update gui
            }
            statement.close(); // Close the statement object
        } catch (Exception exe) { // Trap SQL errors
            gui.putStatus("Error in querying from Database " + '\n' + exe.toString());
        }
    }

    /**
     * Close the database Connection and exit the application
     */
    private void exitApplication() {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException ex) {
                gui.putStatus(" Error in closing the connection: ");
                gui.appendStatus(" " + ex.toString());
            }
        }
        System.exit(0);
    }
}